/****** Object:  StoredProcedure [dbo].[sp_Delete_HierarchyDetail]    Script Date: 06/30/2015 14:05:39 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[sp_Delete_HierarchyDetail]
@HierarchyDetailID int
AS
CREATE TABLE #nodes (HierarchyDetailID int primary key)
INSERT INTO #nodes (HierarchyDetailID) values (@HierarchyDetailID)
WHILE @@rowcount > 0
    INSERT INTO #nodes 
    SELECT DISTINCT child.HierarchyDetailID 
    FROM oHierarchyDetail child
    INNER JOIN #nodes parent on child.HierarchyFatherID = parent.HierarchyDetailID
    WHERE child.HierarchyDetailID NOT IN  (SELECT HierarchyDetailID FROM #nodes)

DELETE
FROM oHierarchyDetail 
WHERE HierarchyDetailID IN (SELECT HierarchyDetailID FROM #nodes)

GO


